package ncc.bs.Plugins.cz;

import nc.vo.pub.BusinessException;
import nccloud.tool.copro.report.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;

/**
 * 构建市财政SQL
 * @author wy
 * @date 20250902
 *
 */
public class CZBusinessSQL {
    private static final SimpleDateFormat ymd = new SimpleDateFormat("yyyy-MM-dd");
    private static final SimpleDateFormat ymdsfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 处理开始、结束时间
     * @param keyMap
     * @return
     * @throws BusinessException
     */
    public  Map businessKeyMap(Map keyMap) throws BusinessException{
        Object begindate = null;
        Object enddate = null;
        Object type = keyMap.get("type");
        if("1".equals(type)) { //  [指定时间段拉取,1];[拉指定的一天,2];
            begindate = keyMap.get("begindate")  == null  ? ymd.format(new Date()) : keyMap.get("begindate") ;
            enddate = keyMap.get("enddate") == null  ? ymd.format(new Date()) : keyMap.get("enddate") ;
            begindate += " 00:00:00";
            enddate += " 23:59:59";
        }else if("2".equals(type)) { // [拉指定的一天,2]
            begindate = keyMap.get("begindate") == null  ? ymd.format(new Date()) : keyMap.get("begindate") ;
            begindate += " 00:00:00";
            enddate = begindate + " 23:59:59";
        }else { // [定时执行, ] 默认同步频率30分钟
            Calendar calendar = Calendar.getInstance();
            String date = ymd.format(calendar.getTime());//当前时间
            begindate = date +" 00:00:00";
            enddate = date +" 23:59:59";
        }
        keyMap.put("begindate",begindate);
        keyMap.put("enddate",enddate);
        return keyMap;
    }

    /**
     * 获取凭证明细表
     * @param keyMap
     * @return
     * @throws BusinessException
     */
    public static String builderCZ_GLA_VOU_DETAIL(Map keyMap) throws BusinessException{
        CZBusinessSQL util = new CZBusinessSQL();
        keyMap = util.businessKeyMap(keyMap);
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT DISTINCT\n" );
        sb.append("     glh.num voucher_no, glh.num vbillcode, xmmc.code pro_code, xmmc.name pro_name,\n" );
        sb.append("     dept.code department_code, dept.name department_name, psndoc.name employee_name,\n" );
        sb.append("     '' gov_bgt_eco_code, '' due_date, '' found_type_code,'' pay_bus_type_code,\n" );
        sb.append("     '' set_mode_code,'' pur_met_code,'' fatype_code,'' cost_type_code,'' bill_date,'' cor_bgt_doc_no,'' budget_level_code,\n" );
        sb.append("     '' sup_bgt_doc_no, '' ori_pro_code, '' bgt_type_code, '' mof_dep_code, '' dep_bgt_eco_code,\n" );
        sb.append("     zcgnflxm.name exp_func_name, zcgnflxm.code AS exp_func_code, xjllxm.code aux01, custmoer.code aux02,\n" );
        sb.append("     supplier.code aux03, jflx.code aux04, zfdx.code aux05, zlxrw.code aux06, srly.code aux07, bkzl.code aux08, xmlx.code aux09,\n" );
        sb.append("     og.pk_group pk_group, og.code acct_set_code, oos.code agency_code, oos.pk_org pk_org,\n" );
        sb.append("     oos.pk_vid pk_org_v, glb.pk_detail vou_det_id, glh.pk_voucher vou_id, glh.year set_year, glh.period acct_period, glb.detailindex vou_seq,\n" );
        sb.append("     glb.explanation vou_det_desc, ba.code gov_acct_cls_code, ba.name gov_acct_cls_name,\n" );
        sb.append("     psndoc.code AS employee_code, zjjh.code AS fund_traobj_type_code, zjjh.code AS fund_tra_obj_no, zjjh.name AS fund_tra_obj_name,\n" );
        sb.append("     vbt.name agency_acct_voucher_type, glh.modifiedtime update_time, glh.creationtime create_time,\n" );
        sb.append("     CASE WHEN glb.localdebitamount = 0 THEN 2 ELSE 1  END dr_cr,\n" );
        sb.append("     CASE WHEN glb.localdebitamount = 0 THEN glb.localcreditamount ELSE glb.localdebitamount END amt,\n" );
        sb.append("     CASE glh.dr  WHEN 0 THEN 2 ELSE 1  END is_deleted \n" );
        sb.append(" FROM\n" );
        sb.append(" gl_voucher glh\n" );
        sb.append(" LEFT JOIN gl_detail glb ON glh.pk_voucher = glb.pk_voucher\n" );
        sb.append(" INNER JOIN org_orgs oos ON glh.pk_org = oos.pk_org\n" );
        sb.append(" LEFT JOIN org_group og ON glh.pk_group = og.pk_group\n" );
        sb.append(" INNER JOIN bd_account ba ON glb.pk_account = ba.pk_account\n" );
        sb.append(" LEFT JOIN bd_vouchertype vbt ON glh.pk_vouchertype = vbt.pk_vouchertype\n" );
        sb.append(" LEFT JOIN gl_docfree1 free ON glb.assid = free.assid\n" );
        sb.append(" LEFT JOIN org_dept dept ON dept.pk_dept = free.f1\n" );
        sb.append(" LEFT JOIN bd_psndoc psndoc ON psndoc.pk_psndoc = free.f2\n" );
        sb.append(" LEFT JOIN bd_areacl dq ON dq.pk_areacl = free.f3\n" );
        sb.append("  LEFT JOIN bd_cust_supplier bcs ON bcs.pk_cust_sup = free.f4\n" );
        sb.append("  LEFT JOIN bd_cust_supplier nbcs ON nbcs.pk_cust_sup = free.f5\n" );
        sb.append("  LEFT JOIN bd_material bm ON bm.pk_material = free.f6\n" );
        sb.append("  LEFT JOIN bd_cashflow xjllxm ON xjllxm.pk_cashflow = free.f7\n" );
        sb.append("  LEFT JOIN bd_inoutbusiclass szxm ON szxm.pk_inoutbusiclass = free.f8\n" );
        sb.append("  LEFT JOIN bd_fundplan zjjh ON zjjh.pk_fundplan = free.f9\n" );
        sb.append("  LEFT JOIN bd_project xm ON xm.pk_project = free.f10\n" );
        sb.append("  LEFT JOIN bd_bankaccsub yhzzh ON yhzzh.pk_bankaccsub = free.f11\n" );
        sb.append("  LEFT JOIN resa_costcenter cbzx ON cbzx.pk_costcenter = free.f12\n" );
        sb.append("  LEFT JOIN bd_customer custmoer ON custmoer.pk_customer = free.f13\n" );
        sb.append("  LEFT JOIN bd_cust_supplier supplier ON supplier.pk_cust_sup = free.f14\n" );
        sb.append("  LEFT JOIN bd_banktype banktype ON banktype.pk_banktype = free.f15\n" );
        sb.append("  LEFT JOIN bd_marbasclass marbasclass ON marbasclass.pk_marbasclass = free.f16\n" );
        sb.append("  LEFT JOIN bd_defdoc def17 ON def17.pk_defdoc = free.f17\n" );
        sb.append("  LEFT JOIN bd_defdoc jflx ON jflx.pk_defdoc = free.f18\n" );
        sb.append("  LEFT JOIN bd_defdoc zfdx ON zfdx.pk_defdoc = free.f19\n" );
        sb.append("  LEFT JOIN bd_defdoc zlxrw ON zlxrw.pk_defdoc = free.f20\n" );
        sb.append("  LEFT JOIN bd_defdoc srly ON srly.pk_defdoc = free.f21\n" );
        sb.append("  LEFT JOIN bd_defdoc bkzl ON bkzl.pk_defdoc = free.f22\n" );
        sb.append("  LEFT JOIN bd_defdoc zcgnflxm ON zcgnflxm.pk_defdoc = free.f23\n" );
        sb.append("  LEFT JOIN bd_defdoc xmmc ON xmmc.pk_defdoc = free.f24\n" );
        sb.append("  LEFT JOIN bd_defdoc xmlx ON xmlx.pk_defdoc = free.f25\n" );
        sb.append("  LEFT JOIN bd_defdoc def26 ON def26.pk_defdoc = free.f26\n" );
        sb.append("  LEFT JOIN bd_defdoc xzhssx ON xzhssx.pk_defdoc = free.f27\n" );
        sb.append("  LEFT JOIN bd_defdoc zfjglb ON zfjglb.pk_defdoc = free.f28\n" );
        sb.append("  LEFT JOIN gl_diffanalysis cyfx ON cyfx.pk_diffanaly = free.f29\n" );
        sb.append("  LEFT JOIN gl_diffanalysis cyfxcs ON cyfxcs.pk_diffanaly = free.f30 \n" );
        sb.append(" WHERE 1 = 1 \n" );
        sb.append("  and glh.dr = 0  AND glb.dr = 0 \n" );
        sb.append("  AND oos.dr = 0  AND og.dr = 0  AND ba.dr = 0 \n" );
        sb.append("  AND glh.adjustperiod <> '00' \n" );
        sb.append("  AND glh.tempsaveflag = 'N' \n" );
        sb.append("  AND glh.discardflag = 'N' \n" );
        sb.append("  and (glh.pk_manager is not null  and  glh.PK_MANAGER <> 'N/A')\n");
        if (null != keyMap.get("org")) {
            sb.append(" and oos.code  in ('" + StringUtils.join( keyMap.get("org").toString().split(","), "','") + "') \n");
        }else{
            sb.append("  AND oos.code = '00101' \n" );
        }
        Object year = ymd.format(new Date()).split("-")[0];
        if (null != keyMap.get("year")){
            year = keyMap.get("year");
        }
        sb.append("  AND glh.year = '"+year+"' \n" );
        if (null != keyMap.get("pk_voucher")){
            sb.append("  AND glh.pk_voucher in ('"+ StringUtils.join( keyMap.get("pk_voucher").toString().split(","), "','")+"') \n" );
        }
        Object begindate = keyMap.get("begindate");// begindate enddate
        Object enddate = keyMap.get("enddate");
        sb.append("  AND ( glh.prepareddate BETWEEN '"+begindate+"' AND '"+enddate+"' \n" );
        sb.append("    or glh.ts between '"+begindate+"' and '"+enddate+"' )   \n");
        sb.append("GROUP BY \n" );
        sb.append("  glh.num, xmmc.code, xmmc.name, dept.code, dept.name, psndoc.code, psndoc.name, xjllxm.code,\n" );
        sb.append("  custmoer.code, supplier.code, jflx.code, zfdx.code, zlxrw.code, srly.code,\n" );
        sb.append("  bkzl.code, xmlx.code, og.pk_group, og.code, oos.code, oos.pk_org, oos.pk_vid,\n" );
        sb.append("  glb.pk_detail, glh.pk_voucher, glh.year, glh.period, glb.detailindex,\n" );
        sb.append("  glb.explanation, ba.code, ba.name,  zjjh.code, szxm.code, vbt.name, glh.modifiedtime, \n" );
        sb.append("  glh.creationtime, glh.dr, zcgnflxm.code, zcgnflxm.name,zjjh.name, glb.localcreditamount, glb.localdebitamount \n" );
        sb.append("ORDER BY \n" );
        sb.append("  glh.num ");
        return sb.toString();
    }

    /**
     * 凭证主表
     * @param keyMap
     * @return
     * @throws BusinessException
     */
    public static String builderCZ_GLA_VOU_HEAD(Map keyMap) throws BusinessException{
        CZBusinessSQL util = new CZBusinessSQL();
        keyMap = util.businessKeyMap(keyMap);
        StringBuilder sql = new StringBuilder();
        sql.append(" \tSELECT\n" );
        sql.append("\t\tvl.attachment, og.pk_group pk_group, oos.pk_org pk_org, oos.pk_vid pk_org_v,\n" );
        sql.append("\t\tog.code acct_set_code, og.name acct_set_name, vl.num vbillcode, oos.code agency_code, \n" );
        sql.append("\t\toos.name agency_name, vl.pk_voucher vou_id, vl.year set_year, vl.period acct_period, \n" );
        sql.append("\t\tvouchertype.name agency_acct_voucher_type, vl.num voucher_no, vl.CW_TOTALCREDIT cr_amt, \n" );
        sql.append("\t\tvl.bug_totalcredit cr_ys_amt, vl.bug_totaldebit dr_ys_amt, vl.CW_TOTALDEBIT dr_amt, \n" );
        sql.append("\t\tvl.explanation voucher_abs, vl.prepareddate inputer_date, vl.checkeddate auditor_date, \n" );
        sql.append("\t\tvl.tallydate poster_date, vl.tallydate voucher_date, zd.user_code inputercode, zd.user_name inputer,\n" );
        sql.append("\t\tsh.user_code auditorcode, sh.user_name auditor, vl.modifiedtime update_time,\n" );
        sql.append("\t\tvl.creationtime create_time, cn.user_code checkercode, cn.user_name checker, jz.user_code postercode, \n" );
        sql.append("\t\tjz.user_name poster,\n" );
        sql.append("\t\tCASE vl.dr  WHEN 0 THEN 2 ELSE 1  END is_deleted,\n" );
        sql.append("\t\tCASE vl.offervoucher  WHEN 'Y' THEN 1 ELSE 0  END red_flag,\n" );
        sql.append("\t\tCASE vl.offervoucher  WHEN 'Y' THEN vl.num ELSE NULL  END red_vou_no,\n" );
        sql.append("\t\tCASE WHEN vl.adjustperiod IS NULL THEN 0 ELSE 1  END is_adjust_period \n" );
        sql.append("\tFROM \n" );
        sql.append("\t\tgl_voucher vl \n" );
        sql.append("\t\tLEFT JOIN bd_vouchertype vouchertype ON vl.pk_vouchertype = vouchertype.pk_vouchertype\n" );
        sql.append("\t\tLEFT JOIN org_group og ON vl.pk_group = og.pk_group\n" );
        sql.append("\t\tLEFT JOIN org_orgs oos ON vl.pk_org = oos.pk_org\n" );
        sql.append("\t\tLEFT JOIN sm_user sh ON vl.pk_checked = sh.cuserid\n" );
        sql.append("\t\tLEFT JOIN sm_user zd ON vl.pk_prepared = zd.cuserid\n" );
        sql.append("\t\tLEFT JOIN sm_user cn ON vl.pk_casher = cn.cuserid\n" );
        sql.append("\t\tLEFT JOIN sm_user jz ON vl.pk_manager = jz.cuserid \n" );
        sql.append("\tWHERE 1 = 1 \n" );
        sql.append("  and (vl.pk_manager is not null  and  vl.PK_MANAGER <> 'N/A') \n");
        sql.append("\t\tAND vl.pk_manager IS NOT NULL  AND vl.tempsaveflag = 'N'  AND vl.discardflag = 'N'  AND vl.dr = 0 \n" );
        sql.append("\t\tAND vl.adjustperiod <> '00' \n" );
        if (null != keyMap.get("org")) {
            sql.append(" and oos.code  in ('" + StringUtils.join( keyMap.get("org").toString().split(","), "','") + "') \n");
        }else{
            sql.append("  AND oos.code = '00101' \n" );
        }
        Object year = ymd.format(new Date()).split("-")[0];
        if (null != keyMap.get("year")){
            year = keyMap.get("year");
        }
        if (null != keyMap.get("pk_voucher")){
            sql.append("  AND vl.pk_voucher in ('"+ StringUtils.join( keyMap.get("pk_voucher").toString().split(","), "','")+"') \n" );
        }
        sql.append("\t\tAND vl.year = '"+year+"' \n" );
        Object begindate = keyMap.get("begindate");// begindate enddate
        Object enddate = keyMap.get("enddate");
        sql.append("\t\tAND ( vl.prepareddate BETWEEN '"+begindate+"'  AND '"+enddate+"' " );
        sql.append("\t\t or vl.ts between '"+begindate+"'   and '"+enddate+"') \n");
        sql.append("\tORDER BY vl.num ASC  ");
        return  sql.toString();
    }
}
